﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ForumDAL
{
    public class PostDAL
    {
        public List<ForumDomainModels.Post> GetPostsByCategory(ForumDomainModels.Category category)
        {
            string connectionString = Properties.Settings.Default.connectionString;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string sql = "SELECT TOP 1000 [Id] ,[Title],[DatePublished],[Content],[AuthorId],[CategoryId] "+
                    " FROM Posts" 
                    +" WHERE CategoryId = @categoryId";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.Add(new SqlParameter("categoryId",category.Id));
                    using(SqlDataReader reader = command.ExecuteReader())
	                {
		                if(!reader.HasRows)
                        {
                            throw new CustomExceptions.NoRowsRetrieved();
                        }
                        else
                        {
                            List<ForumDomainModels.Post> posts = new List<ForumDomainModels.Post>();                                       
                            while(reader.Read())
                            {
                                ForumDomainModels.Mappers.PostMapper postMapper = new ForumDomainModels.Mappers.PostMapper();
                                ForumDomainModels.Post currentPost = postMapper.DataFromDatabaseToPostDomainModel(reader);
                                posts.Add(currentPost);
                            }
                            return posts;
                        }
	                }
                }
            }
        }

        public void InsertPost(ForumDomainModels.Post post)
        {
            string connectionString = Properties.Settings.Default.connectionString;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string sql = "INSERT INTO Posts([Title] ,[DatePublished],[Content],[AuthorId],[CategoryId]) "+
                    " VALUES(@title,@datePublished,@content,@authorId,@categoryId)";
                using (SqlCommand command = new SqlCommand(sql,connection))
                {
                    command.Parameters.Add(new SqlParameter("title",post.Title));
                    command.Parameters.Add(new SqlParameter("datePublished",post.DatePublished));
                    command.Parameters.Add(new SqlParameter("content",post.Content));
                    command.Parameters.Add(new SqlParameter("authorId",post.AuthorId));
                    command.Parameters.Add(new SqlParameter("categoryId",post.CategoryId));
                    int affectedRows = command.ExecuteNonQuery();
                    if (affectedRows == 0)
                    {
                        throw new CustomExceptions.NotInsertedRowsInDatabase();
                    }
                }
            }
        }

        public DateTime GetLatestPostTimeToCategory(ForumDomainModels.Category category)
        {
            string connectionString = Properties.Settings.Default.connectionString;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string sql = "SELECT TOP 1 [DatePublished] " +
                    "FROM [ForumDb].[dbo].[Posts] "+
                    "WHERE CategoryId = @categoryId "+
                    "ORDER BY [DatePublished] desc";
                using (SqlCommand command = new SqlCommand(sql,connection))
                {
                    command.Parameters.Add(new SqlParameter("categoryId", category.Id));
                    DateTime lastPostTime = new DateTime();
                    using(SqlDataReader reader =  command.ExecuteReader())
	                {
                        if (!reader.HasRows)
                        {
                            return default(DateTime);
                        }
                        else
                        {
                            reader.Read();
                            lastPostTime = (DateTime)reader["DatePublished"];
                            return lastPostTime;
                        }
	                }
                }
            }
        }

        public int GetPostCountByCategory(ForumDomainModels.Category category)
        {
            string connectionString = Properties.Settings.Default.connectionString;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string sql = "SELECT COUNT(*) "+
                    "FROM [ForumDb].[dbo].[Posts] "+
                    "WHERE CategoryId = @categoryId";
                using (SqlCommand command = new SqlCommand(sql,connection))
                {
                    command.Parameters.Add(new SqlParameter("categoryId",category.Id));
                    int postCount = (int)command.ExecuteScalar();
                    return postCount;
                }
            }
        }

        public ForumDomainModels.Post GetPostById(ForumDomainModels.Post post)
        {
            string connectionString = Properties.Settings.Default.connectionString;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string sql = "SELECT [Id], [Title], [DatePublished], [Content] ,[AuthorId], [CategoryId]  " +
                    "FROM [ForumDb].[dbo].[Posts] "+
                    "WHERE Id = @id";
                using (SqlCommand command = new SqlCommand(sql,connection))
                {
                    command.Parameters.Add(new SqlParameter("id",post.Id));
                    using(SqlDataReader reader = command.ExecuteReader())
	                {
		                if (!reader.HasRows)
	                    {
		                    throw new CustomExceptions.NoRowsRetrieved();
	                    }
                        else
                        {
                            reader.Read();
                            ForumDomainModels.Post retrievedPost = new ForumDomainModels.Post();
                            retrievedPost.Id = (int)reader["Id"];
                            retrievedPost.Title = (string) reader["Title"];
                            retrievedPost.DatePublished = (DateTime) reader["DatePublished"];
                            retrievedPost.Content = (string)reader["Content"];
                            retrievedPost.CategoryId = (int)reader["CategoryId"];
                            retrievedPost.AuthorId = (int)reader["AuthorId"];
                            return retrievedPost;
                        }
	                }
                }
            }
        }
    }
}
